package sc.server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

public class Database {
	private static Database instance;

	/** JavaDB connection */
	private static Connection conn;
	private static Statement sql;

	/** SQL statements */
	private static PreparedStatement containerCreate = null;
	private static PreparedStatement containerRead = null;
	private static PreparedStatement containerSave = null;

	/**
	 * Creates an instance of Database
	 * 
	 * @throws SQLException
	 * 
	 * @throws SQLException
	 */
	private Database() {
		try {
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
			conn = DriverManager.getConnection("jdbc:derby:db;create=true");
		} catch (Exception e) {
		}
	}

	/**
	 * Gets the database Connection.
	 */
	public static Connection getConnection() {
		if (instance == null)
			instance = new Database();
		return conn;
	}

	/**
	 * Closes the database.
	 * 
	 * @throws SQLException
	 */
	public static void close() throws SQLException {
		sql.close();
		conn.close();
	}

	/**
	 * Initialises the database.
	 * 
	 * @throws SQLException
	 */
	public void initialise() throws SQLException {
		Statement sql = conn.createStatement();
		sql
				.execute("create table container ("
						+ "id integer not null primary key generated always as identity "
						+ "(start with 1, increment by 1), type integer, "
						+ "parent integer references container(id),"
						+ "x integer, y integer, z integer )");
	}

	public static PreparedStatement getContainerCreate() {
		if (containerCreate == null)
			try {
				containerCreate = Database
						.getConnection()
						.prepareStatement(
								"insert into container (type, parent, x, y, z) values (?, ?, ?, ?, ?)",
								Statement.RETURN_GENERATED_KEYS);
			} catch (SQLException e) {
			}
		return containerCreate;
	}

	public static PreparedStatement getContainerRead() {
		if (containerRead == null)
			try {
				containerRead = Database.getConnection().prepareStatement(
						"select * from container where id=?");
			} catch (SQLException e) {
			}
		return containerRead;
	}

	public static PreparedStatement getContainerSave() {
		if (containerSave == null)
			try {
				containerSave = Database.getConnection().prepareStatement(
						"update container set parent=?, x=?, "
								+ " y=?, z=? where id=?");
			} catch (SQLException e) {
			}
		return containerSave;
	}
}
